package DB;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.swing.JOptionPane;

import javab.Logs;
import javab.User;

public class DBUtil {
	Connection con;
	PreparedStatement ps;
	ResultSet rs;
	SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	
	//数据库连接
	public Connection getDBConnection(String type) {
		con=DBFactory.createConnection(type);
		return con;
	}
	
	//日志文件的操作
	//账号搜索
	public ArrayList<Logs> queryById(String id){
		ArrayList<Logs> arlist=new ArrayList<Logs>();
		try {
			ps=con.prepareStatement("select * from logs where id like '%"+id+"%' order by time desc");
			rs=ps.executeQuery();
			while(rs.next()) {
				Logs lg=new Logs();
				lg.setTime(rs.getString(1));
				lg.setOperating(rs.getString(2));
				lg.setBalance(rs.getInt(3));
				lg.setId(rs.getString(4));
				arlist.add(lg);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "账号搜索失败");
		}
		return arlist;
		
	}
	
	//时间搜索
	public ArrayList<Logs> queryByTime(String time){
		ArrayList<Logs> arlist=new ArrayList<Logs>();
		try {
			ps=con.prepareStatement("select * from logs where time like '%"+time+"%' order by time desc");
			rs=ps.executeQuery();
			while(rs.next()) {
				Logs lg=new Logs();
				lg.setTime(rs.getString(1));
				lg.setOperating(rs.getString(2));
				lg.setBalance(rs.getInt(3));
				lg.setId(rs.getString(4));
				arlist.add(lg);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "时间搜索失败");
		}
		return arlist;
		
	}
	
	//金额
	public ArrayList<Logs> queryByBalance(String balance){
		ArrayList<Logs> arlist=new ArrayList<Logs>();
		try {
			ps=con.prepareStatement("select * from logs where number like '%"+balance+"%' order by time desc");
			rs=ps.executeQuery();
			while(rs.next()) {
				Logs lg=new Logs();
				lg.setTime(rs.getString(1));
				lg.setOperating(rs.getString(2));
				lg.setBalance(rs.getInt(3));
				lg.setId(rs.getString(4));
				arlist.add(lg);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "金额搜索失败");
		}
		return arlist;
		
	}
	
	//操作
	public ArrayList<Logs> queryByOpert(String operating){
		ArrayList<Logs> arlist=new ArrayList<Logs>();
		try {
			ps=con.prepareStatement("select * from logs where operating like '%"+operating+"%' order by time desc");
			rs=ps.executeQuery();
			while(rs.next()) {
				Logs lg=new Logs();
				lg.setTime(rs.getString(1));
				lg.setOperating(rs.getString(2));
				lg.setBalance(rs.getInt(3));
				lg.setId(rs.getString(4));
				arlist.add(lg);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "操作查询失败");
		}
		return arlist;
		
	}
	
	
	
	//更新数据存款
	public void updateAdd(int num,String id) {
		con=DBFactory.createConnection("mysql");
		try {
			
			PreparedStatement psql=con.prepareStatement("update users set balance=balance+? where id=?");
			psql.setInt(1, num);
			psql.setString(2, id);
			psql.executeUpdate();
			
			PreparedStatement psqla=con.prepareStatement("insert into logs values(?,?,?,?)");
			psqla.setString(1, df.format(new Date()));
			psqla.setString(2, "存款");
			psqla.setInt(3, num);
			psqla.setString(4, id);
			psqla.executeUpdate();
			
			JOptionPane.showMessageDialog(null, "存钱成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "存钱失败");
		}
		
	}
	
	//取款
	public void updateless(int num,String id) {
		con=DBFactory.createConnection("mysql");
		System.out.println("取钱");
		System.out.println(num);
		System.out.println(id);
		if(con==null) {
			System.out.println("数据库连接错误");
		}
		try {
			PreparedStatement psql=con.prepareStatement("update users set balance=balance-? where id=?");
			psql.setInt(1, num);
			psql.setString(2, id);
			psql.executeUpdate();
			
			PreparedStatement psqla=con.prepareStatement("insert into logs values(?,?,?,?)");
			psqla.setString(1, df.format(new Date()));
			psqla.setString(2, "取款");
			psqla.setInt(3, num);
			psqla.setString(4, id);
			psqla.executeUpdate();
			
			JOptionPane.showMessageDialog(null, "取钱成功");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "取钱错误");
		}
		
	}

	//用户文件
	//用户信息
	public ArrayList<User> queryById1(String id){
		ArrayList<User> arlist=new ArrayList<User>();
		try {
			ps=con.prepareStatement("select * from users where id like '%"+id+"%'");
			rs=ps.executeQuery();
			while(rs.next()) {
				User u=new User();
				u.setId(rs.getString(1));
				u.setName(rs.getString(2));
				u.setBalance(rs.getInt(3));
				u.setPassword(rs.getString(4));
				arlist.add(u);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
//			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "数据库查询错误");
		}

		return arlist;
	}
	
	
	
	//数据库关闭
	public void closeAll() {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(ps!=null) {
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(con!=null) {
			try {
				con.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
//				e.printStackTrace();
				JOptionPane.showMessageDialog(null, "断开数据库错误");
			}
		}
	}

}
